﻿
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<销售提成1>
-- =============================================
CREATE PROCEDURE [dbo].[proc_SalesCommission_GetList] 
(
	@StartIndex int,
	@EndIndex INT,
	@CompanyId INT,
	@DepId INT,
	@UserName NVARCHAR(200),
	@StartDate NVARCHAR(10),
	@EndDate NVARCHAR(10),
	@ListType INT
	
)
AS
BEGIN
	
	Declare @sRq Datetime,@eRq Datetime,@usercount int
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	select @usercount=count( Id) from employee where username=@UserName 
	if(@usercount<1)
	begin
		return;
	end

	Declare @bmbh_T  varchar(30)
	--Select @bmbh_T=bmbh From department Where id=@DepId
	
		--销售提成 当月
		SELECT 
		1 AS TableID,
		id
		,OId
		,OrderId
		,CusId
		,CusName
		,YingShouPrice
		,ShiShouPrice
		,ISNULL((SELECT SUM(p.SalePrice - p.CostPrice) FROM Project p WHERE p.OrderId=[order].OId),0) AS maoli --毛利
		,Salesman
		,SalesmanAccount
		,StateId
		,IsNull((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
		,WayId
		,WayName
		,TypeId
		,TypeName
		,CreateDate
		,ISNULL(FinishDate,'1900-01-01 00:00:00') AS FinishDate
		,0 AS OrderChangePrice
	    FROM [Order] 
	    WHERE SalesmanAccount=@UserName
		AND CreateDate>=@sRq 
		AND CreateDate<=@eRq
		AND NOE_Flag=0
		AND (TypeId=@ListType OR @ListType=0)
		--ORDER BY o1.CreateDate
		
		UNION ALL
		
		SELECT * FROM (
		--补录提成
		SELECT 
		2 AS TableID,
		id
		,OId
		,OrderId
		,CusId
		,CusName
		,YingShouPrice
		,ShiShouPrice
		,ISNULL((SELECT SUM(p.SalePrice - p.CostPrice) FROM Project p WHERE p.OrderId=[order].OId),0) AS maoli 
		,Salesman
		,SalesmanAccount
		,StateId
		,IsNull((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
		,WayId
		,WayName
		,TypeId
		,TypeName
		,CreateDate
		,ISNULL(FinishDate,'1900-01-01 00:00:00') AS FinishDate
		,0 AS OrderChangePrice
	    FROM [Order]
	    WHERE SalesmanAccount=@UserName
		AND CreateDate<@sRq --往月成单
		AND FinishDate>=@sRq --本月完结
		AND FinishDate<=@eRq
		AND (TypeId=@ListType OR @ListType=0)--新增
		AND StateId>=5 --完结
		AND NOE_Flag=0
		
		UNION 
		
		--补录变更
		SELECT 
		4 AS TableID,
		[Order].id
		,[Order].OId
		,[Order].OrderId
		,[Order].CusId
		,[Order].CusName
		,[Order].YingShouPrice
		,[Order].ShiShouPrice
		--,ISNULL((SELECT SUM(p.SalePrice - p.CostPrice) FROM Project p WHERE p.OrderId=[order].OId),0) AS maoli 
		,0 AS maoli
		,[Order].Salesman
		,[Order].SalesmanAccount
		,[Order].StateId
		,IsNull((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
		,[Order].WayId
		,[Order].WayName
		,TypeId
		,TypeName
		,CreateDate
		,ISNULL(FinishDate,'1900-01-01 00:00:00') AS FinishDate
		,[Order_Change].newje-[Order_Change].oldje AS OrderChangePrice
	    FROM [Order] JOIN [Order_Change] ON [Order].OId=[Order_Change].Oid
		WHERE [Order_Change].shqrq>=@sRq	AND [Order_Change].shqrq<=@eRq --当月变更
		AND [Order_Change].oldje<>[Order_Change].newje --变更前后金额改变
		AND CreateDate<@sRq --往月成单
		AND SalesmanAccount=@UserName --本人成单
		AND StateId>=5 --已完结
		AND (TypeId=@ListType OR @ListType=0) --新增
		AND NOE_Flag=0
		AND Order_Change.flag=2
		) abc
		
		UNION ALL
		
		--续费提成
		SELECT 
		3 AS TableID,
		o1.id
		,o1.OId
		,o1.OrderId
		,o1.CusId
		,o1.CusName
		,o1.YingShouPrice
		,o1.ShiShouPrice
		,ISNULL((SELECT SUM(p.SalePrice - p.CostPrice) FROM Project p WHERE p.OrderId=o1.OId),0) AS maoli 
		,o1.Salesman
		,o1.SalesmanAccount
		,o1.StateId
		,IsNull((Select Title From Order_State Where Id=o1.StateId),'---') As StateName
		,o1.WayId
		,o1.WayName
		,o1.TypeId
		,o1.TypeName
		,o1.CreateDate
		,ISNULL(o1.FinishDate,'1900-01-01 00:00:00') AS FinishDate
		--,0 AS OrderChangePrice(如果要求首次续费完结，则将order_item换成Project，下面Where条件里的CreateDate也需换成FinishDate)
		,(Select Count(0) From order_item oi Where oi.oid=o1.OId And oi.ProductId In(1)) AS OrderChangePrice
	    FROM [order] o1
	    WHERE id IN(
			SELECT MIN(o2.Id) FROM [order] o2 WHERE  --根据销售的新增客户CusId，取续费成单的最小ID
				CusId IN (SELECT DISTINCT o3.CusId FROM [order] o3 WHERE o3.SalesmanAccount=@UserName AND o3.TypeId=1 ) --取销售的新增成单客户CusId
				AND o2.TypeId=2 
				GROUP BY o2.CusId ) --Group by 客户CusId去重
		AND o1.CreateDate>=@sRq AND o1.CreateDate<=@eRq
		AND NOE_Flag=0
		ORDER BY 1 ASC,6 ASC,2 DESC		
END